HW#5 --- last modified January 28 2019 19:00:28..

Solution set.

Due date: Dec 10

Files to be submitted:
  Hw5.zip

Purpose: To gain experience with more advanced SQL queries. To gain experience using triggers, views, and JDBC.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

CLO3 -- Use a database management system's bulk loader to populate a database.

CLO4 -- Write simple transactions using JDBC and ODBC, or similar programmer interfaces in other languages.

Specification:

As with previous assignment this homework consists of two parts, a written part and a coding/experiment part. Do the following problems out of the book and write them up in the file Hw4.pdf that you include in your Hw4.zip file your submit: Exercise 6.3.2, 6.3.7, 6.4.6, 6.4.7.

For the coding part of the homework, I want you to use the Mysql DBMS. First, I want you to make a file MovieExec.sql (also turned in you Hw5.zip) containing the SQL DML commands to do the following:

1. Create the two tables: MovieExec(name, address, certNo, netWorth) and Studio(name, address, presidentCertifcate). presidentCertificate should have on it a foreign key constraint which references MovieExec.certno.
2. A view PublicMovieExec(name, address, certNo) which might be used by public users to see the first three columns of the MovieExec table, but not the newWorth column.
3. Triggers which are executed before a row is inserted into the MovieExec table or if a row is updated which set the movie executives netWorth to a 1000000 if the value for netWorth is below 1000000.

Next I want you to make a CSV files MovieExec.csv and Studio.csv each containing at least 10 rows of data. You can make these files in a spreadsheet like Excel by just saving the sheet in CSV format. Put these two files in your Hw5.zip file. Also, put in your Hw5.zip the transcript of you issuing a mysqlimport command to read the data in these files into the tables you created above. Put this in transcript.txt

For the last part of the homework I want you to write a program WhichExec.java. It will be compiled by the grader from the command line using the syntax:

javac WhichExec.java

This program will then be tested based on the grader's version of MovieExec.csv and Studio.csv for populating the two tables described above. it will be run from the command line using a syntax like:

java WhichExec public_or_private part_of_studio_name

For example,

java WhichExec public MGM
java WhichExec private Fox

When run, this program will make a JDBC connection to the database hw5 running on localhost which should contain the tables and views created earlier. The user, password, and connection string data should be defined as constants towards the top of the WhichExec class for the grader to modify. After connecting to the database, it should execute a query that uses either PublicMovieExec or MovieExec depending on whether the first argument was public or private that returns all executive row information for movie executives that are presidents of studios whose names contain the second argument. For example, "java WhichExec private Fox" would use the MovieExec and Studios tables to return the name, address, certNo, netWorth of movie executives who are presidents of studio whose names contain "Fox". So it would return the information about the presidents of both the Fox Searchlight and 20th Century Fox studios. You should order the output in increasing alphabetical order.

Point Breakdown